﻿using Dapper;
using FRMS.Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json;
using FRMS.BUS;
using FRMS.Helper;
using Microsoft.Win32;
using System.Security.Cryptography;

namespace FRMS.DAL
{
    public class DeviceDAL
    {
        public bool UpdateThirdPartyDevice(QueryInfoReturnData queryInfoReturnData)
        {
            string strSql = string.Format("UPDATE device_info SET DeviceIP='{0}',Workmode={2} WHERE DeviceNO='{1}'", queryInfoReturnData.deviceIP, queryInfoReturnData.deviceID, queryInfoReturnData.liftMode);
            return SqlHelper<DeviceInfo>.SqlExecute(strSql);
        }

        public bool UpdateVoucherState(string personid, string deviceid)
        {
            string strSql = string.Format("UPDATE voucher_info SET Lssuestate=1 WHERE OwnerID=(SELECT ID FROM owner_info WHERE IsDelete=0 AND UUID='{0}') AND DeviceID=(SELECT ID FROM device_info WHERE DeviceNO='{1}' AND IsDelete=0)", personid, deviceid);
            return SqlHelper<DeviceInfo>.SqlExecute(strSql);
        }

        public bool UpdateVoucherState(string personid, string deviceid,string devicename)
        {
            string strSql = string.Format("UPDATE voucher_info SET Lssuestate=1 WHERE OwnerID=(SELECT ID FROM owner_info WHERE IsDelete=0 AND UUID='{0}') AND DeviceID=(SELECT ID FROM device_info WHERE DeviceNO='{1}' AND DeviceName='{2}' AND IsDelete=0)", personid, deviceid,devicename);
            return SqlHelper<DeviceInfo>.SqlExecute(strSql);
        }

        public bool DeleteVoucher(string personid, string deviceid)
        {
            string strSql = string.Format("DELETE FROM voucher_info WHERE OwnerID=(SELECT ID FROM owner_info WHERE IsDelete=0 AND UUID='{0}') AND DeviceID=(SELECT ID FROM device_info WHERE DeviceNO='{1}' AND IsDelete=0)", personid, deviceid);
            return SqlHelper<DeviceInfo>.SqlExecute(strSql);
        }

        public bool DeleteVoucher(string personid, string deviceid,string devicename)
        {
            string strSql = string.Format("DELETE FROM voucher_info WHERE OwnerID=(SELECT ID FROM owner_info WHERE IsDelete=0 AND UUID='{0}') AND DeviceID=(SELECT ID FROM device_info WHERE DeviceNO='{1}' AND DeviceName='{2}' AND IsDelete=0)", personid, deviceid,devicename);
            return SqlHelper<DeviceInfo>.SqlExecute(strSql);
        }

        public bool ClearVoucher(string deviceid)
        {
            string strSql = string.Format("DELETE FROM voucher_info WHERE DeviceID=(SELECT ID FROM device_info WHERE DeviceNO='{0}' AND IsDelete=0)", deviceid);
            return SqlHelper<DeviceInfo>.SqlExecute(strSql);
        }

        public bool ClearVoucher(string deviceid,string devicename)
        {
            string strSql = string.Format("DELETE FROM voucher_info WHERE DeviceID=(SELECT ID FROM device_info WHERE DeviceNO='{0}' AND DeviceName='{1}' AND IsDelete=0)", deviceid,devicename);
            return SqlHelper<DeviceInfo>.SqlExecute(strSql);
        }

        public int CheckDeviceIp(string devip, int projectid)
        {
            string strSql = "SELECT COUNT(1) FROM device_info WHERE IsDelete=0 AND DeviceIP='" + devip + "' AND projectID=" + projectid;
            return SqlHelper<DeviceInfo>.SqlScalar(strSql);
        }

        public int CheckDeviceByIp(string devip, int projectid)
        {
            string strSql = "SELECT ID FROM device_info WHERE IsDelete=0 AND DeviceIP='" + devip + "' AND projectID=" + projectid;
            return SqlHelper<DeviceInfo>.SqlScalar(strSql);
        }

        public int CheckDev(string devid)
        {
            string strSql = "SELECT count(1) FROM device_info WHERE IsDelete=0 AND DeviceNO='" + devid + "'";
            return SqlHelper<DeviceInfo>.SqlScalar(strSql);
        }

        public bool UpdateDevice(RegisterInfo register)
        {
            string strSql = string.Format("UPDATE device_info SET DeviceIP='{0}',DeviceVersion='{1}',DeviceMask='{2}' WHERE DeviceNO='{3}'", register.ip, register.version, register.mask, register.deviceID);
            return SqlHelper<DeviceInfo>.SqlExecute(strSql);
        }

        public bool AddDevice(DeviceInfo deviceInfo)
        {
            string strSql = string.Format(@"INSERT INTO device_info(ElevatorID,DeviceName,DeviceNO,DeviceType,DeviceIP,DeviceState,DeviceVersion,DeviceMask,DeviceMac,projectID,m_lUserID)
                                             VALUES({0},'{1}','{2}',{3},'{4}','{5}','{6}','{7}','{8}','{9}',{10})", deviceInfo.ElevatorID, deviceInfo.DeviceName, deviceInfo.DeviceNO, deviceInfo.DeviceType, deviceInfo.DeviceIP, "离线", deviceInfo.DeviceVersion, deviceInfo.DeviceMask, deviceInfo.DeviceMac, deviceInfo.projectID, -1);
            return SqlHelper<DeviceInfo>.SqlExecute(strSql);
        }

        public int AddDevice_1(DeviceInfo deviceInfo)
        {
            string strSql = string.Format(@"INSERT INTO device_info(ElevatorID,DeviceName,DeviceNO,DeviceType,DeviceIP,DeviceState,DeviceVersion,DeviceMask,DeviceMac,projectID,m_lUserID)
                                             VALUES({0},'{1}','{2}',{3},'{4}','{5}','{6}','{7}','{8}','{9}',{10});select @@IDENTITY;", deviceInfo.ElevatorID, deviceInfo.DeviceName, deviceInfo.DeviceNO, deviceInfo.DeviceType, deviceInfo.DeviceIP, "离线", deviceInfo.DeviceVersion, deviceInfo.DeviceMask, deviceInfo.DeviceMac, deviceInfo.projectID, -1);
            return SqlHelper<DeviceInfo>.SqlScalar(strSql);
        }

        public bool UpdateDevice(DeviceInfo deviceInfo)
        {
            string strSql = string.Format(@"UPDATE device_info SET ElevatorID={0},DeviceName='{1}',DeviceIP='{2}',DeviceType='{3}',DeviceMac='{4}',DeviceMask='{5}',DeviceVersion='{6}',DeviceNO='{8}' WHERE ID={7}",
                deviceInfo.ElevatorID, deviceInfo.DeviceName, deviceInfo.DeviceIP, deviceInfo.DeviceType, deviceInfo.DeviceMac, deviceInfo.DeviceMask, deviceInfo.DeviceVersion, deviceInfo.ID, deviceInfo.DeviceNO);
            return SqlHelper<DeviceInfo>.SqlExecute(strSql);
        }

        public bool UpdateDeviceState(string state, string deviceip, string deviceno, string control)
        {
            string strSql = string.Format(@"UPDATE device_info SET DeviceState='{0}',ControllerState='{3}',DeviceStateUpdateTime='{4}' WHERE DeviceNO='{1}' AND DeviceIP='{2}'", state, deviceno, deviceip, control, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            return SqlHelper<DeviceInfo>.SqlExecute(strSql);
        }

        public bool UpdateDeviceState(string state, string deviceip, string deviceno, string control,string devicename)
        {
            string strSql = string.Format(@"UPDATE device_info SET DeviceState='{0}',ControllerState='{3}',DeviceStateUpdateTime='{4}' WHERE DeviceNO='{1}' AND DeviceIP='{2}' AND DeviceName='{5}'", state, deviceno, deviceip, control, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"),devicename);
            return SqlHelper<DeviceInfo>.SqlExecute(strSql);
        }

        public bool DelDevice(DeviceInfo deviceInfo)
        {
            string strSql = "UPDATE device_info SET IsDelete=1 WHERE ID=" + deviceInfo.ID;
            return SqlHelper<DeviceInfo>.SqlExecute(strSql);
        }

        public bool BatchDelDeviceIds(string deviceids)
        {
            string strSql = "UPDATE device_info SET DeviceState='离线' WHERE DeviceNO IN(" + deviceids + ")";
            return SqlHelper<DeviceInfo>.SqlExecute(strSql);
        }

        public bool BatchUpdateDeviceIds(string deviceids)
        {
            string strSql = string.Format("UPDATE device_info SET DeviceState='离线',ControllerState='离线',DeviceStateUpdateTime='{1}',ControllerStateUpdateTime='{1}' WHERE DeviceNO IN({0})", deviceids, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            return SqlHelper<DeviceInfo>.SqlExecute(strSql);
        }

        public List<DeviceInfo> QueryDeviceInfo(DeviceInfo deviceInfo)
        {
            string strSql = "SELECT * FROM vi_device";
            string sqlwhere = string.Empty;

            if (deviceInfo.projectID > 0)
            {
                sqlwhere += " projectID=" + deviceInfo.projectID + " AND";
            }
            if (deviceInfo.BuildID > 0)
            {
                sqlwhere += " BuildID=" + deviceInfo.BuildID + " AND";
            }
            if (deviceInfo.ElevatorID > 0)
            {
                sqlwhere += " ElevatorID=" + deviceInfo.ElevatorID + " AND";
            }
            sqlwhere += " IsDelete =0 AND";

            if (sqlwhere.Length > 0)
            {
                strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }
            if (deviceInfo.Recordhead > -1 && deviceInfo.Recordtail > 0)
            {
                strSql += " LIMIT " + deviceInfo.Recordhead + "," + deviceInfo.Recordtail;
            }

            return SqlHelper<DeviceInfo>.SqlList(strSql);
        }

        public int QueryDeviceInfoCount(DeviceInfo deviceInfo)
        {
            string strSql = "SELECT COUNT(1) FROM device_info";
            string sqlwhere = string.Empty;

            if (deviceInfo.projectID > 0)
            {
                sqlwhere += " projectID=" + deviceInfo.projectID + " AND";
            }
            if (deviceInfo.BuildID > 0)
            {
                sqlwhere += " BuildID=" + deviceInfo.BuildID + " AND";
            }
            if (deviceInfo.ElevatorID > 0)
            {
                sqlwhere += " ElevatorID=" + deviceInfo.ElevatorID + " AND";
            }
            sqlwhere += " IsDelete =0 AND";

            if (sqlwhere.Length > 0)
            {
                strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }

            return SqlHelper<DeviceInfo>.SqlScalar(strSql);
        }

        public bool CheckVoucher(DeviceInfo deviceInfo)
        {
            string strSql = "SELECT COUNT(1) FROM voucher_info where IsDelete=0 AND DeviceID=" + deviceInfo.ID;
            int count = SqlHelper<DeviceInfo>.SqlScalar(strSql);
            if (count > 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        public List<DeviceInfo> QueryDeviceList(DeviceInfo deviceInfo)
        {
            string strSql = "SELECT * FROM vi_device";
            string sqlwhere = string.Empty;

            if (deviceInfo.projectID > 0)
            {
                sqlwhere += " projectID=" + deviceInfo.projectID + " AND";
            }
            if (deviceInfo.BuildID > 0)
            {
                sqlwhere += " BuildID=" + deviceInfo.BuildID + " AND";
            }
            if (deviceInfo.ElevatorID > 0)
            {
                sqlwhere += " ElevatorID=" + deviceInfo.ElevatorID + " AND";
            }
            if (!string.IsNullOrEmpty(deviceInfo.DeviceNO))
            {
                sqlwhere += " DeviceNO like '%" + deviceInfo.DeviceNO + "%' AND";
            }
            if (!string.IsNullOrEmpty(deviceInfo.DeviceName))
            {
                sqlwhere += " DeviceName like '%" + deviceInfo.DeviceName + "%' AND";
            }
            sqlwhere += " IsDelete =0 AND";

            if (sqlwhere.Length > 0)
            {
                strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }
            if (deviceInfo.Recordhead > -1 && deviceInfo.Recordtail > 0)
            {
                strSql += " LIMIT " + deviceInfo.Recordhead + "," + deviceInfo.Recordtail;
            }

            return SqlHelper<DeviceInfo>.SqlList(strSql);
        }

        public int QueryDeviceListCount(DeviceInfo deviceInfo)
        {
            string strSql = "SELECT COUNT(1) FROM vi_device";
            string sqlwhere = string.Empty;

            if (deviceInfo.projectID > 0)
            {
                sqlwhere += " projectID=" + deviceInfo.projectID + " AND";
            }
            if (deviceInfo.BuildID > 0)
            {
                sqlwhere += " BuildID=" + deviceInfo.BuildID + " AND";
            }
            if (deviceInfo.ElevatorID > 0)
            {
                sqlwhere += " ElevatorID=" + deviceInfo.ElevatorID + " AND";
            }
            if (!string.IsNullOrEmpty(deviceInfo.DeviceNO))
            {
                sqlwhere += " DeviceNO like '%" + deviceInfo.DeviceNO + "%' AND";
            }
            if (!string.IsNullOrEmpty(deviceInfo.DeviceName))
            {
                sqlwhere += " DeviceName like '%" + deviceInfo.DeviceName + "%' AND";
            }
            sqlwhere += " IsDelete =0 AND";

            if (sqlwhere.Length > 0)
            {
                strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }

            return SqlHelper<DeviceInfo>.SqlScalar(strSql);
        }

        public List<TypeName> QueryDevType()
        {
            string strSql = "SELECT * FROM device_type";

            return SqlHelper<TypeName>.SqlList(strSql);
        }

        public DeviceInfo LoadDeviceInfo(int id)
        {
            string strSql = "SELECT * FROM vi_device WHERE ID=" + id;
            return SqlHelper<DeviceInfo>.SqlSingleData(strSql);
        }

        public DeviceInfo LoadDeviceByIp(string device_ip)
        {
            string strSql = "SELECT * FROM vi_device WHERE DeviceIP='" + device_ip + "' AND IsDelete=0";
            return SqlHelper<DeviceInfo>.SqlSingleData(strSql);
        }

        public bool UpdateWorkmode(DeviceInfo deviceInfo)
        {
            string strSql = string.Format(@"UPDATE device_info SET Workmode='{0}',WorkmodeUpdateTime='{1}' WHERE DeviceUuid='{2}'",
                deviceInfo.Workmode, deviceInfo.WorkmodeUpdateTime, deviceInfo.DeviceUuid);
            return SqlHelper<DeviceInfo>.SqlExecute(strSql);
        }

        /// <summary>
        /// 更新设备在线状态
        /// </summary>
        /// <param name="deviceInfo"></param>
        /// <returns></returns>
        public void UpdateDevicestate(int eid, int m_lUserID)
        {
            string devstate = "";
            if (m_lUserID < 0)
            {
                devstate = "离线";
            }
            else
            {
                devstate = "在线";
            }
            string strSql = string.Format(@"UPDATE device_info SET m_lUserID={0},DeviceState='{1}' WHERE ID={2}", m_lUserID, devstate, eid);
            try
            {
                using (var conn = DataFactory.GetSQLConnection())
                {

                    int count = conn.Execute(strSql);

                    //if (count > 0)
                    //{
                    //    return true;
                    //}
                    //return false;
                }
            }
            catch
            {
                //return false;
            }
        }

        public DeviceInfo QueryDevByHK(string device_ip)
        {
            string strSql = "SELECT * FROM vi_device WHERE ElevatorID=(SELECT ElevatorID FROM device_info WHERE DeviceIP='" + device_ip + "' AND DeviceType=1 AND IsDelete=0) AND DeviceType=2 AND IsDelete=0";
            return SqlHelper<DeviceInfo>.SqlSingleData(strSql);
        }

        public bool UpdateSession(string device_no, string ip, string session, string uuid, string mac)
        {
            string strSql = string.Format("UPDATE device_info SET Session='{0}',DeviceUuid='{3}',DeviceMac='{4}',DeviceNO='{1}',RegisterTime='{5}',DeviceState='在线',DeviceStateUpdateTime='{5}' WHERE DeviceIP='{2}' AND IsDelete=0", session, device_no, ip, uuid, mac, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            return SqlHelper<DeviceInfo>.SqlSessionExecute(strSql);
        }

        public bool UpdateDeviceState(int deviceid, string state)
        {
            string strSql = string.Format("UPDATE device_info SET DeviceState='{0}',DeviceStateUpdateTime='{2}' WHERE ID={1};INSERT device_record (DeviceID,State,UpdateTime)VALUES({1},'{0}','{2}')", state, deviceid, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            return SqlHelper<DeviceInfo>.SqlExecute(strSql);
        }

        public bool UpdateControllerState(DeviceInfo deviceInfo)
        {
            string strSql = string.Format(@"UPDATE device_info SET ControllerState='{0}',ControllerStateUpdateTime='{1}' WHERE DeviceUuid='{2}'",
                deviceInfo.ControllerState, deviceInfo.ControllerStateUpdateTime, deviceInfo.DeviceUuid);
            return SqlHelper<DeviceInfo>.SqlExecute(strSql);
        }
        public bool AddControllerState(DeviceInfo deviceInfo)
        {
            //string strS = "select ID from device_info where DeviceNO= '" + deviceInfo.DeviceNO + "'";
            //int id1 = SqlHelper<DeviceInfo>.SqlScalar(strS);
            string strSql = string.Format(@"INSERT INTO controllerstate_record(DeviceID,ControllerState,ControllerStateUpdateTime)
                                             VALUES({0},'{1}','{2}')", deviceInfo.ID, deviceInfo.ControllerState, deviceInfo.ControllerStateUpdateTime);
            return SqlHelper<ControllerStateRecord>.SqlExecute(strSql);

        }

        public bool Addworkmode(DeviceInfo deviceInfo)
        {
            //string strS = "select ID from device_info where DeviceNO= '" + deviceInfo.DeviceNO + "'";
            //int id1 = SqlHelper<DeviceInfo>.SqlScalar(strS);
            string strSql = string.Format(@"INSERT INTO workmodestate_record(DeviceID,WorkModeState,WorkModeUpdateTime)
                                             VALUES({0},{1},'{2}')", deviceInfo.ID, deviceInfo.Workmode, deviceInfo.WorkmodeUpdateTime);
            return SqlHelper<WorkmodeRecord>.SqlExecute(strSql);
        }

        /// <summary>
        /// 获取Type=1即梯控面板的设备信息
        /// </summary>
        /// <returns></returns>
        /// 
        public List<DeviceInfo> QueryDeviceInf(string eid)
        {
            string strSql = "SELECT * FROM device_info where DeviceUuid='" + eid + "'";
            return SqlHelper<DeviceInfo>.SqlList(strSql);
        }

        public DeviceInfo LoadDeviceByUUID(string uuid)
        {
            string strSql = "SELECT * FROM vi_device WHERE DeviceUuid='" + uuid + "' AND IsDelete=0";
            return SqlHelper<DeviceInfo>.SqlSingleData(strSql);
        }

        public bool ImportDevice(DeviceInfo deviceInfo)
        {
            string strSql = string.Format(@"INSERT INTO device_info(ElevatorID,DeviceName,DeviceType,DeviceIP,DeviceState,projectID,m_lUserID)
                                             VALUES((SELECT ID FROM elevator_info WHERE ElevatorName='{0}' AND projectID={1} AND IsDelete=0 LIMIT 1),'{2}',1,'{3}','离线',{1},-1)"
                                          , deviceInfo.ElevatorName, deviceInfo.projectID, deviceInfo.DeviceName, deviceInfo.DeviceIP);

            return SqlHelper<DeviceInfo>.SqlExecute(strSql);
        }

        public bool ResetState()
        {
            string strSql = "UPDATE device_info SET DeviceState='离线',ControllerState='离线'";
            return SqlHelper<DeviceInfo>.SqlExecute(strSql);
        }

        public List<DeviceInfo> QueryOnLine()
        {
            string strSql = "SELECT * FROM device_info WHERE DeviceState='在线' AND IsDelete=0";
            return SqlHelper<DeviceInfo>.SqlList(strSql);
        }

        public bool UpdateToken(string token, string ip)
        {
            string strSql = string.Format("UPDATE device_info SET Token='{0}',DeviceState='在线',DeviceStateUpdateTime='{1}' WHERE DeviceIP='{2}' AND IsDelete=0", token, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), ip);
            return SqlHelper<DeviceInfo>.SqlSessionExecute(strSql);
        }

        public bool UpdateDeviceState(string deviceip, string state)
        {
            string strSql = string.Format("UPDATE device_info SET DeviceState='{0}',DeviceStateUpdateTime='{2}' WHERE DeviceIP='{1}'", state, deviceip, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            return SqlHelper<DeviceInfo>.SqlExecute(strSql);
        }

        public List<ElevatorInfo> QueryEleByDeviceNo(string deviceno)
        {
            string strSql = "SELECT * FROM elevator_info WHERE ID IN(SELECT ElevatorID FROM device_info WHERE DeviceNO='" + deviceno + "' AND IsDelete=0) AND IsDelete=0";
            return SqlHelper<ElevatorInfo>.SqlList(strSql);
        }

    }
}
